import time,re
import pandas as pd
import redis
import pickle
import logging
import pywencai
import akshare as ak
import pandas as pd
import configparser
import datetime
from sqlalchemy import create_engine,text
import pymysql
import os




pymysql.install_as_MySQLdb()


log_format = "%(asctime)s - %(levelname)s - %(process)d - %(filename)s:%(lineno)d - %(message)s"
date_format = "%Y-%m-%d %H:%M:%S"  # 精确到秒
logging.basicConfig(level=logging.DEBUG, format=log_format, datefmt=date_format)

pid = os.getpid()
query_date = datetime.datetime.now().strftime('%Y%m%d')

# 日志文件路径
log_file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), f'log/{pid}.log')

# 创建一个 handler，用于写入日志文件
file_handler = logging.FileHandler(log_file_path)
file_handler.setFormatter(logging.Formatter(log_format, date_format))
# 添加 handler 到 logger
logging.getLogger().addHandler(file_handler)

# 初始化配置解析器
config = configparser.ConfigParser()

# 读取配置文件
import os
current_dir = os.path.dirname(os.path.abspath(__file__))
config.read(current_dir+'/config.ini', encoding='utf-8')


# 获取Redis的配置信息
redis_host = config.get('Redis', 'host')
redis_port = config.getint('Redis', 'port')
redis_db = config.getint('Redis', 'db')
redis_password = config.get('Redis', 'password')
r = redis.Redis(host=redis_host, port=redis_port, db=redis_db, password=redis_password)

mysql_port = config.getint('mysql', 'port')
mysql_host = config.get('mysql', 'host')
mysql_db = config.get('mysql', 'db')
import urllib.parse
mysql_password = urllib.parse.quote(config.get('mysql', 'password'))
mysql_user = config.get('mysql', 'user')
db_url = f'mysql://{mysql_user}:{mysql_password}@{mysql_host}:{mysql_port}/{mysql_db}'

engine = create_engine(db_url,pool_size=20,max_overflow=20,pool_recycle=60)


# subscriber = r.pubsub()
# subscriber.subscribe('bjzt_channel')

query_date = datetime.datetime.now().strftime('%Y%m%d')
# query_date = datetime.datetime.now().strftime('%Y-%m-%d')


def get_next_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    # 筛选出所有晚于给定交易日的日期
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] > pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=True)  # 按日期升序排序
    next_trade_date = date_df["trade_date"].values[0]  # 获取最接近给定日期的下一个交易日
    return next_trade_date.strftime("%Y-%m-%d")  # 格式化日期

# 定义获取前一个交易日的函数
def get_pre_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[0]
    return pre_trade_date.strftime("%Y%m%d")

def get_pre_trade_date_n(trade_date,n):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[n-1]
    return pre_trade_date.strftime("%Y%m%d")



def main(query_time,next_trade_date):
    sql = f'''
                    select a.* from analysis_stock_step_test a inner join  
        (select name,min(`timestamp`) min_time from analysis_stock_step_test where Date(`timestamp`) ='{query_time}' 
         and amount>0.3 and concept_zt_count=0  and rps20<99 and zhsl>50 
         and suggestion like '%%领涨%%' group by name) b 
        on a.name=b.name and a.timestamp = b.min_time
        '''
    print(sql)
    df = pd.read_sql(sql, engine)
    df = df.drop_duplicates(subset=['name'])
    print(df)
    df = df.sort_values(by="timestamp",ascending=True)




    sql = f'''
                    select code,open next_open,price price_n from (
                select * from real_market_info_tdx union
                select * from real_market_info_tdx_h rmith ) a
                where `timestamp`='{next_trade_date} 09:31:00'
        '''
    print(sql)
    df_next_day = pd.read_sql(sql, engine)
    # df_next_day = df_next_day[["code","price","open"]]

    df = pd.merge(df,df_next_day,how="inner",on="code")
    df["profile"] = (df["next_open"]-df["price"])*100/df["price"]
    df["query_date"] = query_time
    df["profile_931"] = (df["next_open"] - df["price"]) * 100 / df["price"]
    pd.set_option('display.max_colwidth', None)
    pd.set_option('display.max_columns', None)
    # print(df["code","name","timestamp",""])

    print(df["profile"].mean())



    return df


def analysis(query_date):
    next_trade_date = get_next_trade_date(query_date)
    df = main(query_date, next_trade_date)
    return df





if __name__ == "__main__":
    l=[]
    l.append(analysis("2024-4-11"))
    # l.append(analysis("2024-4-26"))
    # l.append(analysis("2024-4-29"))
    # l.append(analysis("2024-4-30"))
    # l.append(analysis("2024-4-23"))
    # l.append(analysis("2024-4-22"))
    # l.append(analysis("2024-4-19"))
    # l.append(analysis("2024-4-18"))
    # l.append(analysis("2024-4-16"))
    # l.append(analysis("2024-4-15"))
    # l.append(analysis("2024-05-06"))
    # l.append(analysis("2024-05-07"))
    # l.append(analysis("2024-05-08"))
    # l.append(analysis("2024-05-09"))

    # l.append(analysis("2024-4-11"))
    df = pd.concat(l)
    print(df)
    df.to_excel(f"result/绩效评估.xlsx", index=False)


    # logging.info("监控进程启动")
    # # main("2024-4-26")
    # main("2024-4-24")
    # main("2024-4-23")
    # main("2024-4-22")
    # main("2024-4-26")
    # main("2024-4-18")
    # main("2024-4-19")
    # main("2024-4-17")
    # main("2024-4-16")
    # main("2024-4-12")
    # main("2024-4-11")










